﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class SkuClassDA : ISkuClassDA
    {
        private string CREATED_BY = "@CREATED_BY";
        private string DESCR = "@DESCR";
        private string DESCR2 = "@DESCR2";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string OWNER_ID = "@OWNER_ID";
        private string SKU_CLASS_ID = "@SKU_CLASS_ID";
        private string SQL_CHECK_SKU_CLASS_ID_UNIQUE = " SELECT COUNT(1) FROM @_@SKU_CLASS WHERE OWNER_ID = @OWNER_ID AND SKU_CLASS_ID = @SKU_CLASS_ID ";
        private string SQL_DELETE_SKU_CLASS = " DELETE FROM @_@SKU_CLASS WHERE OWNER_ID = @OWNER_ID AND SKU_CLASS_ID = @SKU_CLASS_ID ";
        private string SQL_INSERT_SKU_CLASS = " INSERT INTO @_@SKU_CLASS ( WH_ID, OWNER_ID, SKU_CLASS_ID, DESCR, DESCR2, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @OWNER_ID, @SKU_CLASS_ID, @DESCR, @DESCR2, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_SKU_CLASS = " SELECT  ROWID, WH_ID, OWNER_ID, SKU_CLASS_ID, DESCR, DESCR2, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@SKU_CLASS ";
        private string SQL_UPDATE_SKU_CLASS = " UPDATE @_@SKU_CLASS SET WH_ID = @WH_ID, DESCR = @DESCR, DESCR2 = @DESCR2, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_CLASS_ID = @SKU_CLASS_ID  ";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckSkuClassIDUnique(string skuClassID, string OwnerID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", OwnerID), new SqlParameter("@SKU_CLASS_ID", skuClassID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_SKU_CLASS_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckSkuClassIDUnique(DataBase dataBase, DbTransaction tran, string skuClassID, string OwnerID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", OwnerID), new SqlParameter("@SKU_CLASS_ID", skuClassID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_SKU_CLASS_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteSkuClass(List<string> skuClassID, List<string> OwnerID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < skuClassID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", OwnerID[j]), new SqlParameter("@SKU_CLASS_ID", skuClassID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_SKU_CLASS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteSkuClass(DataBase dataBase, DbTransaction tran, List<string> skuClassID, List<string> OwnerID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < skuClassID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", OwnerID[j]), new SqlParameter("@SKU_CLASS_ID", skuClassID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_SKU_CLASS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllSkuClass(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SKU_CLASS.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllSkuClass(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SKU_CLASS.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public SkuClassInfo GetSkuClassByID(string skuclassID, string OwnerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SKU_CLASS.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_CLASS_ID = @SKU_CLASS_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", OwnerID), new SqlParameter("@SKU_CLASS_ID", skuclassID) };
            SkuClassInfo skuClassInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    skuClassInfo = new SkuClassInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_CLASS_ID"].ToString(), reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return skuClassInfo;
        }

        public SkuClassInfo GetSkuClassByID(DataBase dataBase, DbTransaction tran, string skuclassID, string OwnerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SKU_CLASS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", OwnerID), new SqlParameter("@SKU_CLASS_ID", skuclassID) };
            SkuClassInfo skuClassInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                skuClassInfo = new SkuClassInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_CLASS_ID"].ToString(), reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return skuClassInfo;
        }

        public DataSet GetSkuClassByQueryList(List<string> sqlWhere, SkuClassQueryEntity skuclassQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_SKU_CLASS.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!skuclassQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, skuclassQuery.CurrentPage, skuclassQuery.PageSize, skuclassQuery.SortField, skuclassQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetSkuClassByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, SkuClassQueryEntity skuclassQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_SKU_CLASS.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!skuclassQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, skuclassQuery.CurrentPage, skuclassQuery.PageSize, skuclassQuery.SortField, skuclassQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertSkuClass(SkuClassInfo skuclassInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    skuclassInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_SkuClass_Parameters(skuclassInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_SKU_CLASS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertSkuClass(DataBase dataBase, DbTransaction tran, SkuClassInfo skuClassInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    skuClassInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_SkuClass_Parameters(skuClassInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_SKU_CLASS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_SkuClass_Parameters(SkuClassInfo skuClassInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_CLASS_ID, SqlDbType.VarChar, 50), new SqlParameter(this.DESCR, SqlDbType.VarChar, 100), new SqlParameter(this.DESCR2, SqlDbType.VarChar, 100), new SqlParameter(this.UDF1, SqlDbType.VarChar, 0x12), new SqlParameter(this.UDF2, SqlDbType.VarChar, 0x12), new SqlParameter(this.UDF3, SqlDbType.VarChar, 0x12), new SqlParameter(this.UDF4, SqlDbType.VarChar, 0x12), new SqlParameter(this.UDF5, SqlDbType.VarChar, 0x12), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), 
                new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(skuClassInfo.WhID))
            {
                paramArray[0].Value = skuClassInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = skuClassInfo.OwnerID;
            paramArray[2].Value = skuClassInfo.SkuClassID;
            if (!string.IsNullOrEmpty(skuClassInfo.Descr))
            {
                paramArray[3].Value = skuClassInfo.Descr;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Descr2))
            {
                paramArray[4].Value = skuClassInfo.Descr2;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Udf1))
            {
                paramArray[5].Value = skuClassInfo.Udf1;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Udf2))
            {
                paramArray[6].Value = skuClassInfo.Udf2;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Udf3))
            {
                paramArray[7].Value = skuClassInfo.Udf3;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Udf4))
            {
                paramArray[8].Value = skuClassInfo.Udf4;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Udf5))
            {
                paramArray[9].Value = skuClassInfo.Udf5;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Udf6))
            {
                paramArray[10].Value = skuClassInfo.Udf6;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Udf7))
            {
                paramArray[11].Value = skuClassInfo.Udf7;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Udf8))
            {
                paramArray[12].Value = skuClassInfo.Udf8;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Udf9))
            {
                paramArray[13].Value = skuClassInfo.Udf9;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(skuClassInfo.Udf10))
            {
                paramArray[14].Value = skuClassInfo.Udf10;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            paramArray[15].Value = skuClassInfo.IsActive;
            paramArray[0x10].Value = skuClassInfo.CreatedBy;
            paramArray[0x11].Value = skuClassInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateSkuClass(SkuClassInfo skuClassInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    skuClassInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_SkuClass_Parameters(skuClassInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_SKU_CLASS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateSkuClass(DataBase dataBase, DbTransaction tran, SkuClassInfo skuClassInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    skuClassInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_SkuClass_Parameters(skuClassInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_SKU_CLASS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

